package com.xindacloud.sms.console.system.service.impl;


import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xindacloud.sms.console.constant.SessionKeys;
import com.xindacloud.sms.console.result.Result;
import com.xindacloud.sms.console.result.ResultGenerator;
import com.xindacloud.sms.console.system.dto.SmsUpdatePricingDTO;
import com.xindacloud.sms.console.system.dto.SmsUserAllowOperatorDTO;
import com.xindacloud.sms.console.system.entity.SmsUserAllowOperator;
import com.xindacloud.sms.console.system.mapper.SmsUserAllowOperatorMapper;
import com.xindacloud.sms.console.system.service.ISmsUserAllowOperatorService;

/**
 * <p>
 * 授权给用户的短信通道 服务实现类
 * </p>
 *
 * @author czc
 * @since 2019-10-08
 */
@Service
public class SmsUserAllowOperatorServiceImpl extends ServiceImpl<SmsUserAllowOperatorMapper, SmsUserAllowOperator> implements ISmsUserAllowOperatorService {

    @Override
    public boolean updateUserAllowOperator(Integer userId, List<String> operatorIds, Boolean status) {

        return lambdaUpdate()
                .set(SmsUserAllowOperator::getStatus, status)
                .in(SmsUserAllowOperator::getOperatorId, operatorIds)
                .eq(SmsUserAllowOperator::getUserId, userId)
                .update();

        /*try {
            final List<SmsUserAllowOperator> allowOperators = lambdaQuery()
                    .eq(SmsUserAllowOperator::getUserId, userId)
                    .in(CollectionUtils.isNotEmpty(operatorIds), SmsUserAllowOperator::getOperatorId, operatorIds)
                    .list();

            List<SmsUserAllowOperator> insertList = new ArrayList<>();
            if (CollectionUtils.isEmpty(allowOperators)) {
                operatorIds.forEach(operatorId -> {
                    final SmsUserAllowOperator build = SmsUserAllowOperator.builder()
                            .userId(userId)
                            .operatorId(operatorId)
                            .status(status)
                            .build();
                    insertList.add(build);

                });
            } else {
                List<SmsUserAllowOperator> updateList = new ArrayList<>();
                for (SmsUserAllowOperator userAllowOperator : allowOperators) {
                    String operatorId = userAllowOperator.getOperatorId();
                    if (operatorIds.contains(operatorId)) {
                        userAllowOperator.setStatus(status);
                        updateList.add(userAllowOperator);
                    } else {
                        final SmsUserAllowOperator build = SmsUserAllowOperator.builder()
                                .userId(userId)
                                .operatorId(operatorId)
                                .status(status)
                                .build();
                        insertList.add(build);
                    }
                }
                if (CollectionUtils.isNotEmpty(updateList)) {
                    updateBatchById(updateList);
                }
            }
            if (CollectionUtils.isNotEmpty(insertList)) {
                saveBatch(insertList);
            }
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
*/
    }


    @Override
    public boolean saveSmsUserAllowOperatorBatch(List<SmsUserAllowOperator> userAllowOperatorList) {
        return saveBatch(userAllowOperatorList);
    }

    @Override
    public boolean deleteUserAllowOperatorByUserId(Integer userId) {
        Wrapper<SmsUserAllowOperator> wrapper = new QueryWrapper<>(SmsUserAllowOperator.builder().userId(userId).build());
        return remove(wrapper);
    }

    @Override
    public boolean updateSmsUserAllowBatch(List<SmsUserAllowOperator> userAllowOperatorList) {
        return updateBatchById(userAllowOperatorList);
    }

    @Override
    public List<SmsUserAllowOperator> querySmsUserAllowOperatorList(List<String> operatorIds, Integer userId) {
        return lambdaQuery()
                .in(SmsUserAllowOperator::getOperatorId, operatorIds)
                .eq(SmsUserAllowOperator::getUserId, userId)
                .list();
    }

    @Override
    public Result importPriceExcel(MultipartFile file, String agentId, String agentGroup) {
        List<SmsUserAllowOperator> infoList = new ArrayList<>();

        try {
            final String originalFilename = file.getOriginalFilename();
            assert originalFilename != null;
            if (originalFilename.substring(originalFilename.lastIndexOf(".")).equalsIgnoreCase(".csv")) {
            	/*if(agentGroup.equalsIgnoreCase("nexmo")) {
            		infoList.addAll(readNexmoCSVFile(file.getInputStream(), agentId));
            	}else if(agentGroup.equalsIgnoreCase("sinch")) {
            		infoList.addAll(readSinchCSVFile(file.getInputStream(), agentId));
            	}else if(agentGroup.equalsIgnoreCase("cm")) {
            		infoList.addAll(readCmCSVFile(file.getInputStream(), agentId));
            	}else {
            		
            	}*/

            } else {
                if (agentGroup.equalsIgnoreCase("nexmo")) {
                    infoList.addAll(readNexmoExcel(file.getInputStream(), agentId));
                } else if (agentGroup.equalsIgnoreCase("sinch")) {
                    infoList.addAll(readSinchExcel(file.getInputStream(), agentId));
                } else if (agentGroup.equalsIgnoreCase("cm")) {
                    infoList.addAll(readCmExcel(file.getInputStream(), agentId));
                } else {

                }

            }
        } catch (IOException | InvalidFormatException e) {
            log.error(e.getMessage(), e);
        }
        if (this.saveBatch(infoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }
    }

    @Override
    public void downloadAllPrice(HttpServletRequest request, HttpServletResponse response,Integer userId) {
        try {
        	QueryWrapper<SmsUserAllowOperator> queryWrapper=new QueryWrapper<>();
        	queryWrapper.eq("user_id", userId);
        	List<SmsUserAllowOperator> bgmExcelDownloads=this.list(queryWrapper);
            //List<SmsUserAllowOperator> bgmExcelDownloads = this.list();
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("获取excel测试表格");

            HSSFRow row = null;

			/*row = sheet.createRow(0);//创建第一个单元格
			row.setHeight((short) (26.25 * 20));
			row.createCell(0).setCellValue("用户信息列表");//为第一行单元格设值
*/
            /*为标题设计空间
             * firstRow从第1行开始
             * lastRow从第0行结束
             *
             *从第1个单元格开始
             * 从第3个单元格结束
             */
            //CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 1);
            //sheet.addMergedRegion(rowRegion);
            row = sheet.createRow(0);
            row.setHeight((short) (22.50 * 20));//设置行高
            row.createCell(0).setCellValue("operatorId(请勿修改)");//为第一个单元格设值
            row.createCell(1).setCellValue("代理商ID(请勿修改)");//为第二个单元格设值
            row.createCell(2).setCellValue("国家简码");//为第三个单元格设值
            row.createCell(3).setCellValue("国家名称");
            row.createCell(4).setCellValue("国家显示名称");
            row.createCell(5).setCellValue("网络");
            row.createCell(6).setCellValue("网络区域码");
            row.createCell(7).setCellValue("运营商名称");
            row.createCell(8).setCellValue("前缀");
            row.createCell(9).setCellValue("成本价");
            row.createCell(10).setCellValue("售价");
            row.createCell(11).setCellValue("默认价格");
            row.createCell(12).setCellValue("货币单位");
            row.createCell(13).setCellValue("type类型");
            row.createCell(14).setCellValue("mcc");
            row.createCell(15).setCellValue("mnc");
            row.createCell(16).setCellValue("userId(请勿修改)");
            row.createCell(17).setCellValue("allowOperatorId(请勿修改)");
            //遍历所获取的数据
            for (int i = 0; i < bgmExcelDownloads.size(); i++) {
                row = sheet.createRow(i + 1);
                SmsUserAllowOperator sp = bgmExcelDownloads.get(i);
                row.createCell(0).setCellValue(sp.getOperatorId());
                row.createCell(1).setCellValue(sp.getAgentId());
                row.createCell(2).setCellValue(sp.getCountryCode());
                row.createCell(3).setCellValue(sp.getCountryName());
                row.createCell(4).setCellValue(sp.getCountryDisplayName());
                row.createCell(5).setCellValue(sp.getNetworkCode());
                row.createCell(6).setCellValue(sp.getNetworkAliases());
                row.createCell(7).setCellValue(sp.getNetworkName());
                row.createCell(8).setCellValue(sp.getPrefix());
                row.createCell(9).setCellValue(String.valueOf(sp.getCostPrice()));
                row.createCell(10).setCellValue(String.valueOf(sp.getPrice()));
                row.createCell(11).setCellValue(String.valueOf(sp.getDefaultPrice()));
                row.createCell(12).setCellValue(sp.getCurrency());
                row.createCell(13).setCellValue(sp.getType());
                row.createCell(14).setCellValue(sp.getMcc());
                row.createCell(15).setCellValue(sp.getMnc());
                row.createCell(16).setCellValue(sp.getUserId());
                row.createCell(17).setCellValue(sp.getAllowOperatorId());
            }
            sheet.setDefaultRowHeight((short) (16.5 * 20));
            //列宽自适应
            for (int i = 0; i <= 13; i++) {
                sheet.autoSizeColumn(i);
            }


            OutputStream os = response.getOutputStream();
            String ename = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
            String fileName = "excel_" + ename + ".xls";
            response.setHeader("Access-Control-Expose-Headers", SessionKeys.SMS_TOKEN + ",Content-Disposition");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Content-Type", "application/vnd.ms-excel");

            wb.write(os);
            response.flushBuffer();
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Result uploadAllPrice(MultipartFile file,Integer userId) {
        List<SmsUserAllowOperator> infoList = null;
        try {
            infoList = readSmsPriceExcel(file.getInputStream(),userId);

        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        if (this.saveOrUpdateBatch(infoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }
	}
	
	 private List<SmsUserAllowOperator> readSmsPriceExcel(InputStream inputStream,Integer userId) throws IOException, InvalidFormatException {
	        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
	        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
	        Sheet sheet = wb.getSheetAt(0);
	        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
	            Row row = sheet.getRow(i);
	            SmsUserAllowOperator info = new SmsUserAllowOperator();
	            info.setUserId(userId);
	            if (Objects.nonNull(row.getCell(0))) {
	                row.getCell(0).setCellType(CellType.STRING);
	                info.setOperatorId(row.getCell(0).getStringCellValue());
	            }
	            if (Objects.nonNull(row.getCell(1))) {
	                row.getCell(1).setCellType(CellType.STRING);
	                info.setAgentId(row.getCell(1).getStringCellValue());
	            }
	            if (Objects.nonNull(row.getCell(2))) {
	                row.getCell(2).setCellType(CellType.STRING);
	                info.setCountryCode(row.getCell(2).getStringCellValue());
	            }
	            if (Objects.nonNull(row.getCell(3))) {
	                row.getCell(3).setCellType(CellType.STRING);
	                info.setCountryName(row.getCell(3).getStringCellValue());
	            }
	            if (Objects.nonNull(row.getCell(4))) {
	                row.getCell(4).setCellType(CellType.STRING);
	                info.setCountryDisplayName(row.getCell(4).getStringCellValue());
	            }
	            
	            if (Objects.nonNull(row.getCell(5))) {
	                row.getCell(5).setCellType(CellType.STRING);
	                info.setNetworkCode(row.getCell(5).getStringCellValue());
	            }
	            if (Objects.nonNull(row.getCell(6))) {
	                row.getCell(6).setCellType(CellType.STRING);
	                info.setNetworkAliases(row.getCell(6).getStringCellValue());
	            }
	            if (Objects.nonNull(row.getCell(7))) {
	                row.getCell(7).setCellType(CellType.STRING);
	                info.setNetworkName(row.getCell(7).getStringCellValue());
	            }
	            if (Objects.nonNull(row.getCell(8))) {
	                row.getCell(8).setCellType(CellType.STRING);
	                info.setPrefix(row.getCell(8).getStringCellValue());
	            }
	            
	            if (Objects.nonNull(row.getCell(9))) {
	                row.getCell(9).setCellType(CellType.STRING);
	                if(StringUtils.isNotEmpty(row.getCell(9).getStringCellValue())) {
	                info.setCostPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(9).getStringCellValue())));
	                }
	            }
	            if (Objects.nonNull(row.getCell(10))) {
	                row.getCell(10).setCellType(CellType.STRING);
	                if(StringUtils.isNotEmpty(row.getCell(10).getStringCellValue())) {
	                info.setPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(10).getStringCellValue())));
	                }
	            }
	            if (Objects.nonNull(row.getCell(11))) {
	                row.getCell(11).setCellType(CellType.STRING);
	                if(StringUtils.isNotEmpty(row.getCell(10).getStringCellValue())) {
	                info.setDefaultPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(11).getStringCellValue())));
	                }
	            }

	            if (Objects.nonNull(row.getCell(12))) {
	            	row.getCell(12).setCellType(CellType.STRING);
	            	info.setCurrency(row.getCell(12).getStringCellValue());
	            }
	            if (Objects.nonNull(row.getCell(13))) {
	            	row.getCell(13).setCellType(CellType.STRING);
	            	info.setType(row.getCell(13).getStringCellValue());
	            }
	            
	            if (Objects.nonNull(row.getCell(14))) {
	            	row.getCell(14).setCellType(CellType.STRING);
	            	info.setMcc(row.getCell(14).getStringCellValue());
	            }
	            if (Objects.nonNull(row.getCell(15))) {
	            	row.getCell(15).setCellType(CellType.STRING);
	            	info.setMnc(row.getCell(15).getStringCellValue());
	            }
	           /* if (Objects.nonNull(row.getCell(16))) {
	            	row.getCell(16).setCellType(CellType.STRING);
	            	info.setUserId(Integer.valueOf(row.getCell(16).getStringCellValue()));
	            }*/
	            if (Objects.nonNull(row.getCell(17))) {
	            	row.getCell(17).setCellType(CellType.STRING);
	            	info.setAllowOperatorId(row.getCell(17).getStringCellValue());
	            }
	            if(StringUtils.isEmpty(info.getCountryCode())) {
	            	continue;
	            }
	            smsPricingList.add(info);
	        }
	        return smsPricingList;
	    }


    @Override
    public Result updateBatchPrice(SmsUpdatePricingDTO smsUpdatePricingDTO) {
        List<SmsUserAllowOperator> infoList = new ArrayList<>();
        List<String> ids = smsUpdatePricingDTO.getOperatorIds();
        if (smsUpdatePricingDTO.getSymbol().equals("+")) {
            for (String operatorId : ids) {
                SmsUserAllowOperator info = this.getById(operatorId);
                info.setPrice(info.getCostPrice().add(smsUpdatePricingDTO.getCountValue()));
                infoList.add(info);
            }
        } else {
            for (String operatorId : ids) {
                SmsUserAllowOperator info = this.getById(operatorId);
                info.setPrice(info.getCostPrice().multiply(smsUpdatePricingDTO.getCountValue()));
                infoList.add(info);
            }
        }
        if (this.updateBatchById(infoList)) {
            return ResultGenerator.genSuccessResult("批量修改价格成功!");
        } else {
            return ResultGenerator.genClientFailResult("批量修改价格失败!");
        }
    }

    private List<SmsUserAllowOperator> readNexmoExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsUserAllowOperator info = new SmsUserAllowOperator();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryCode(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                info.setCountryName(row.getCell(1).getStringCellValue());
                info.setCountryDisplayName(row.getCell(1).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(2))) {
                info.setNetworkCode(row.getCell(2).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(3))) {
                info.setNetworkAliases(row.getCell(3).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(4))) {
                info.setNetworkName(row.getCell(4).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(5))) {
                info.setPrefix(row.getCell(5).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(6))) {
                final BigDecimal price = BigDecimal.valueOf(row.getCell(6).getNumericCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            if (Objects.nonNull(row.getCell(0)) && Objects.nonNull(row.getCell(5))) {
                info.setCurrency("EUR");
                info.setAgentId(agentId);
                info.setType("mobile");
                smsPricingList.add(info);
            }
        }
        return smsPricingList;
    }

    private List<SmsUserAllowOperator> readSinchExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsUserAllowOperator info = new SmsUserAllowOperator();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryCode(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                final BigDecimal price = BigDecimal.valueOf(row.getCell(1).getNumericCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            if (Objects.nonNull(row.getCell(2))) {
                info.setCountryName(row.getCell(2).getStringCellValue());
                info.setCountryDisplayName(row.getCell(2).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                info.setPrefix(row.getCell(3).getStringCellValue());
            }
            info.setCurrency("EUR");
            info.setAgentId(agentId);
            info.setType("mobile");
            smsPricingList.add(info);
        }
        return smsPricingList;
    }

    private List<SmsUserAllowOperator> readCmExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsUserAllowOperator info = new SmsUserAllowOperator();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryName(row.getCell(0).getStringCellValue());
                info.setCountryDisplayName(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                info.setCountryCode(row.getCell(1).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                String str = row.getCell(3).getStringCellValue();
                StringBuffer sb = new StringBuffer();
                String prefix = "";
                String[] strs = str.split(",");
                if (strs.length > 0) {
                    for (String sstr : strs) {
                        String ss = sstr.substring(2);
                        sb.append(ss).append(",");
                    }
                    String ss = sb.toString();
                    prefix = ss.substring(0, ss.length() - 1);
                } else {
                    prefix = str.substring(0, str.length() - 1);
                }

                info.setPrefix(prefix);
            }
            if (Objects.nonNull(row.getCell(5))) {
                row.getCell(5).setCellType(CellType.STRING);
                final BigDecimal price = new BigDecimal(row.getCell(5).getStringCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            info.setCurrency("EUR");
            info.setAgentId(agentId);
            info.setType("mobile");
            smsPricingList.add(info);
        }
        return smsPricingList;
    }


    @Override
    public Result updateUserPrice(SmsUserAllowOperatorDTO smsUserAllowOperatorDTO) {
        SmsUserAllowOperator smsUserAllowOperator = new SmsUserAllowOperator();
        try {
            BeanUtils.copyProperties(smsUserAllowOperatorDTO, smsUserAllowOperator);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (this.saveOrUpdate(smsUserAllowOperator)) {
            return ResultGenerator.genSuccessResult("保存或修改成功！");
        } else {
            return ResultGenerator.genClientFailResult("保存或修改失败！");
        }
    }


    @Override
    public boolean updateStatusByOperatorIds(List<String> operatorIds, Boolean status) {
        return lambdaUpdate()
                .set(SmsUserAllowOperator::getStatus, status)
                .in(SmsUserAllowOperator::getOperatorId, operatorIds)
                .update();
    }


	@Override
	public Result importUserPriceExcel(MultipartFile file, Integer userId) {
		List<SmsUserAllowOperator> infoList = null;
        try {
            infoList = readSmsInportPriceExcel(file.getInputStream(),userId);

        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        if (this.saveBatch(infoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }
	}
	
	private List<SmsUserAllowOperator> readSmsInportPriceExcel(InputStream inputStream,Integer userId) throws IOException, InvalidFormatException {
        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            SmsUserAllowOperator info = new SmsUserAllowOperator();
            info.setUserId(userId);
            info.setAllowOperatorId(UUID.randomUUID().toString().replaceAll("-", ""));
            if (Objects.nonNull(row.getCell(0))) {
                row.getCell(0).setCellType(CellType.STRING);
                info.setOperatorId(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                row.getCell(1).setCellType(CellType.STRING);
                info.setAgentId(row.getCell(1).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(2))) {
                row.getCell(2).setCellType(CellType.STRING);
                info.setCountryCode(row.getCell(2).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                info.setCountryName(row.getCell(3).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(4))) {
                row.getCell(4).setCellType(CellType.STRING);
                info.setCountryDisplayName(row.getCell(4).getStringCellValue());
            }
            
            if (Objects.nonNull(row.getCell(5))) {
                row.getCell(5).setCellType(CellType.STRING);
                info.setNetworkCode(row.getCell(5).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(6))) {
                row.getCell(6).setCellType(CellType.STRING);
                info.setNetworkAliases(row.getCell(6).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(7))) {
                row.getCell(7).setCellType(CellType.STRING);
                info.setNetworkName(row.getCell(7).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(8))) {
                row.getCell(8).setCellType(CellType.STRING);
                info.setPrefix(row.getCell(8).getStringCellValue());
            }
            
            if (Objects.nonNull(row.getCell(9))) {
                row.getCell(9).setCellType(CellType.STRING);
                if(StringUtils.isNotEmpty(row.getCell(9).getStringCellValue())) {
                info.setCostPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(9).getStringCellValue())));
                }
            }
            if (Objects.nonNull(row.getCell(10))) {
                row.getCell(10).setCellType(CellType.STRING);
                if(StringUtils.isNotEmpty(row.getCell(10).getStringCellValue())) {
                info.setPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(10).getStringCellValue())));
                }
            }
            if (Objects.nonNull(row.getCell(11))) {
                row.getCell(11).setCellType(CellType.STRING);
                if(StringUtils.isNotEmpty(row.getCell(10).getStringCellValue())) {
                info.setDefaultPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(11).getStringCellValue())));
                }
            }

            if (Objects.nonNull(row.getCell(12))) {
            	row.getCell(12).setCellType(CellType.STRING);
            	info.setCurrency(row.getCell(12).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(13))) {
            	row.getCell(13).setCellType(CellType.STRING);
            	info.setType(row.getCell(13).getStringCellValue());
            }
            
            if (Objects.nonNull(row.getCell(14))) {
            	row.getCell(14).setCellType(CellType.STRING);
            	info.setMcc(row.getCell(14).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(15))) {
            	row.getCell(15).setCellType(CellType.STRING);
            	info.setMnc(row.getCell(15).getStringCellValue());
            }
            /*if (Objects.nonNull(row.getCell(16))) {
            	row.getCell(16).setCellType(CellType.STRING);
            	info.setUserId(Integer.valueOf(row.getCell(16).getStringCellValue()));
            }*/
            if (Objects.nonNull(row.getCell(17))) {
            	row.getCell(17).setCellType(CellType.STRING);
            	info.setAllowOperatorId(row.getCell(17).getStringCellValue());
            }
            if(StringUtils.isEmpty(info.getCountryCode())) {
            	continue;
            }
            smsPricingList.add(info);
        }
        return smsPricingList;
    }

}
